package net.starwifi.duobao.utils;

import org.apache.poi.ss.usermodel.*;
import org.springframework.web.multipart.MultipartFile;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by Administrator on 2016/3/8.
 */
public class ExcelUtils {

    public static List<Map<String,Object>> readExcel(MultipartFile file)
    {
        List<Map<String,Object>> list = new ArrayList<Map<String,Object>>();
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        try {

            Workbook workbook = WorkbookFactory.create(file.getInputStream()); //这种方式 Excel 2003/2007/2010 都是可以处理的
            int sheetCount = workbook.getNumberOfSheets();  //Sheet的数量
            //遍历每个Sheet
            for (int s = 0; s < sheetCount; s++) {
                Sheet sheet = workbook.getSheetAt(s);
                int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数
                //遍历每一行
                for (int r = 0; r < rowCount; r++) {
                    Map<String,Object> map = new HashMap<String, Object>();
                    Row row = sheet.getRow(r);
                    int cellCount = row.getPhysicalNumberOfCells(); //获取总列数
                    Cell cell = row.getCell(1);
                    int cellType = cell.getCellType();
                    String cellValue = null;
                    switch(cellType) {
                        case Cell.CELL_TYPE_STRING: //文本
                            cellValue = cell.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_NUMERIC: //数字、日期
                            if(DateUtil.isCellDateFormatted(cell)) {
                                cellValue = fmt.format(cell.getDateCellValue()); //日期型
                            }
                            else {
                                cellValue = String.format("%.0f", cell.getNumericCellValue());//15145117218，取整！
                            }
                            break;
                        case Cell.CELL_TYPE_BOOLEAN: //布尔型
                            cellValue = String.valueOf(cell.getBooleanCellValue());
                            break;
                        case Cell.CELL_TYPE_BLANK: //空白
                            cellValue = cell.getStringCellValue();
                            break;
                        case Cell.CELL_TYPE_ERROR: //错误
                            cellValue = "错误";
                            break;
                        case Cell.CELL_TYPE_FORMULA: //公式
                            cellValue = "错误";
                            break;
                        default:
                            cellValue = "错误";
                    }
                    System.out.print(cellValue + "    ");
                    map.put("mobile", cellValue);
                    list.add(map);
                }
            }

        }
        catch (Exception e) {
            e.printStackTrace();
        }

        return list;
    }
}
